package com.fsxgt.datagrid.ds.handler;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.util.StringUtils;
import com.fsxgt.datagrid.ds.bo.ColumnInfo;
import com.fsxgt.datagrid.ds.bo.DatasourceJdbcBO;
import com.fsxgt.datagrid.ds.exception.DsException;
import com.fsxgt.datagrid.ds.tool.MetaSqlInterface;
import com.fsxgt.datagrid.ds.utils.JdbcConstants;
import com.fsxgt.datagrid.ds.utils.ParamUtil;
import com.google.common.collect.Lists;

import lombok.extern.slf4j.Slf4j;
import ru.yandex.clickhouse.BalancedClickhouseDataSource;
import ru.yandex.clickhouse.settings.ClickHouseProperties;
@Slf4j
public class RdbmsHandler implements DbHandler {
	
	public RdbmsHandler() {
		
	}

	public DataSource getDatasource() {
		return datasource;
	}

	public void setDatasource(DataSource datasource) {
		this.datasource = datasource;
	}

	private DataSource getDataSource(DatasourceJdbcBO datasourceJdbcBO, String testSql) throws DsException {

		if (datasourceJdbcBO.getDatasource().equals(JdbcConstants.CLICKHOUSE)) {

			ClickHouseProperties properties = new ClickHouseProperties();
			properties.setPassword(datasourceJdbcBO.getJdbcPassword());
			properties.setUser(datasourceJdbcBO.getJdbcUsername());
			BalancedClickhouseDataSource balancedClickhouseDataSource = new BalancedClickhouseDataSource(
					datasourceJdbcBO.getJdbcUrl(), properties);
			balancedClickhouseDataSource.scheduleActualization(10, TimeUnit.SECONDS);

			return balancedClickhouseDataSource;

		} else {
			DruidDataSource dataSource = new DruidDataSource();

			dataSource.setUsername(datasourceJdbcBO.getJdbcUsername());
			dataSource.setPassword(datasourceJdbcBO.getJdbcPassword());
			dataSource.setUrl(datasourceJdbcBO.getJdbcUrl());
			dataSource.setDriverClassName(datasourceJdbcBO.getJdbcDriverClass());
			dataSource.setMaxActive(20);
			dataSource.setMinIdle(0);
			dataSource.setInitialSize(1);
			dataSource.setMaxWait(1000 * 60 * 2);
			dataSource.setMinEvictableIdleTimeMillis(1000 * 60 * 1);
			dataSource.setTimeBetweenEvictionRunsMillis(1000 * 60 * 1);

			dataSource.setTestWhileIdle(true);
			dataSource.setTestOnBorrow(false);
			dataSource.setTestOnReturn(false);
			dataSource.setKeepAlive(true);
			dataSource.setValidationQuery(testSql);
			try {
				dataSource.init();
			} catch (Exception e) {
				try {
					dataSource.close();
				} catch (Exception e1) {
					;
				}
				throw new DsException(e);
			}

			return dataSource;
		}

	}

	protected static final Logger logger = LoggerFactory.getLogger(RdbmsHandler.class);
	/**
	 * 用于获取查询语句
	 */
	private MetaSqlInterface metaSqlInterface;

	private DataSource datasource;

	private JdbcTemplate template;

	private volatile boolean init = false;
	/**
	 * 当前数据库名
	 */
	private String currentSchema = "";

	public String getCurrentSchema() {
		if (init) {
			return currentSchema;
		}
		synchronized (this) {
			if (!init) {
				currentSchema = getSchema(config.getJdbcUsername());
				init = true;
			}
		}
		return currentSchema;
	}

	private String datasourceType;

	public String getDatasourceType() {
		return datasourceType;
	}

	private DatasourceJdbcBO config = null;

	public DatasourceJdbcBO getConfig() {
		return config;
	}

	/**
	 * 构造方法
	 *
	 * @param jobDatasource
	 * @throws DsException
	 * @throws SQLException
	 */
	public RdbmsHandler(DatasourceJdbcBO datasourceJdbcBO, MetaSqlInterface metaSqlInterface) throws DsException {

		try {
			this.datasource = this.getDataSource(datasourceJdbcBO, metaSqlInterface.getTestSql());
		} catch (Exception e) {
			throw new DsException(e);
		}

		this.metaSqlInterface = metaSqlInterface;
		template = new JdbcTemplate(datasource);
		this.datasourceType = datasourceJdbcBO.getDatasource();
		this.config = datasourceJdbcBO;
	}

	public MetaSqlInterface getMetaSqlInterface() {
		return metaSqlInterface;
	}

	public void setMetaSqlInterface(MetaSqlInterface metaSqlInterface) {
		this.metaSqlInterface = metaSqlInterface;
	}

	// 根据connection获取schema
	private String getSchema(String jdbcUsername) {
		String schema = template.execute(new ConnectionCallback<String>() {

			@Override
			public String doInConnection(Connection con) throws SQLException, DataAccessException {
				// TODO Auto-generated method stub

				String schema = null;
				if (con.getCatalog() != null) {
					schema = con.getCatalog();
				} else {
					schema = con.getSchema();
				}
				return schema;
			}

		});
		if (schema == null && jdbcUsername != null) {
			schema = jdbcUsername.toUpperCase();
		}
		return schema;
	}

	public List<ColumnInfo> getColumnNames(String schema, String tableName)
			throws SQLException {

		List<ColumnInfo> fullColumn = Lists.newArrayList();
		// 获取指定表的所有字段
		Statement statement = null;

		String querySql = metaSqlInterface.getSQLQueryFieldsByTableName(schema, tableName);
		// 获取查询指定表所有字段的sql语句

		logger.info("querySql: {}", querySql);

		List<ColumnInfo> list = getResultSetMetaData(querySql);

		List<ColumnInfo> columns = buildColumns(tableName, list);

		return columns;
	}

	


	private List<ColumnInfo> buildColumns(String tableName, List<ColumnInfo> res) throws SQLException {


		if (tableName != null
				&& (datasourceType.equals(JdbcConstants.MYSQL) || datasourceType.equals(JdbcConstants.ORACLE))) {
			template.execute(new ConnectionCallback<String>() {

				@Override
				public String doInConnection(Connection con) throws SQLException, DataAccessException {
					// TODO Auto-generated method stub

					DatabaseMetaData databaseMetaData = con.getMetaData();

					ResultSet resultSet = databaseMetaData.getPrimaryKeys(null, null, tableName);

					while (resultSet.next()) {
						String name = resultSet.getString("COLUMN_NAME");
						res.forEach(e -> {
							if (e.getName().equals(name)) {
								e.setIsPrimaryKey(true);
							} else {
								e.setIsPrimaryKey(false);
							}
						});
					}

					return null;
				}
			});
			if (tableName != null) {
				res.forEach(e -> {
					String sqlQueryComment = metaSqlInterface.getSQLQueryComment(getCurrentSchema(), tableName,
							e.getName());
					// 查询字段注释
					e.setComment(template.queryForObject(sqlQueryComment, String.class));
				});
			}

		}
		return res;
	}

	public List<String> getTableNames(String schema) throws SQLException {
		
		String sql =  getSQLQueryTables(schema);
		
		log.info(sql);

		List<String> tables = this.template.queryForList(sql, String.class);

		return tables;
	}



	protected String getSQLQueryTables(String schema) {
		
		if(!StringUtils.isEmpty(schema)) {
			return metaSqlInterface.getSQLQueryTables(schema);
		}else {
			return metaSqlInterface.getSQLQueryTables();
		}
		
	}



	public static void main(String args[]) {
		String querySql = "select * from dfsfdsf where 1=1";
		String s = querySql.toUpperCase();

		System.out.println("SELCT COUNT(1) FROM " + querySql);
	}

	public List<ColumnInfo> getColumnsBySql(String querySql) throws SQLException {

		List<String> res = Lists.newArrayList();

		String sql = querySql;

		// 获取所有字段
		List<ColumnInfo> list = getResultSetMetaData(sql);

		List<ColumnInfo> columns = buildColumns(null, list);

		return columns;
	}

	private List<ColumnInfo> getResultSetMetaData(String querySql) {
		List<ColumnInfo> metaDataList = template.query(querySql, new ResultSetExtractor<List<ColumnInfo>>() {
			@Override
			public List<ColumnInfo> extractData(ResultSet rs) throws SQLException, DataAccessException {
				// TODO Auto-generated method stub

				List<ColumnInfo> list = new ArrayList();

				ResultSetMetaData resultSetMetaData = rs.getMetaData();

				int columnCount = resultSetMetaData.getColumnCount();
				for (int i = 1; i <= columnCount; i++) {

					list.add(new ColumnInfo(resultSetMetaData.getColumnLabel(i), resultSetMetaData.getColumnTypeName(i),
							resultSetMetaData.isNullable(i)));
				}

				return list;
			}
		});
		return metaDataList;
	}

	public Object getMaxIdVal(String tableName, String key, String srcWhere) {
		
		String where = "";
		if(srcWhere!=null&&srcWhere.trim().length()>0) {
			where=" where " + srcWhere;
		}
		

		String sql = metaSqlInterface.getMaxId(tableName, key)+where;
		
		log.info(sql);
		

		return template.queryForObject(sql, Object.class);
	}

	public String getMaxSqlForExp(String sql, String exp) throws DsException {

		List<String> list = ParamUtil.getFindStringList(sql, "[\\s\\t]+[Ff][Rr][Oo][Mm][\\s\\t]+");
		if (list.size() == 0) {
			throw new DsException("找不到from关键字");
		}
		sql = "select max(" + exp + ") " + sql.substring(sql.indexOf(list.get(0)));

		log.info(sql);
		
		return sql;
	}

	public String getSqlForCount(String sql) throws DsException {

		return "SELECT COUNT(1) as \"count\" from  (" + sql + ") b";

	}

	public List<String> getSchemas() {

		List<String> schemas = new ArrayList();

		String sql = metaSqlInterface.getSQLQueryTableSchema();

		log.info(sql);
		
		if (sql != null) {
			schemas = template.queryForList(sql, String.class);
		}

		return schemas;
	}



	public int update(String sql, List params) {
		PreparedStatementCreator creator = new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				// TODO Auto-generated method stub

				PreparedStatement pre = con.prepareStatement(sql);
				int i = 0;
				for (Object p : params) {
					pre.setObject(++i, p);
				}
				return pre;
			}
		};
		return template.update(creator);
	}

	public Object queryForObject(String sql) {
		return template.queryForObject(sql, Object.class);
	}

	public List<Map<String, Object>> queryForList(String sql, List params) {

		return template.query(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				// TODO Auto-generated method stub
				PreparedStatement pre = con.prepareStatement(sql);
				int i = 0;
				for (Object p : params) {
					pre.setObject(++i, p);
				}
				return pre;
			}
		}, new RowMapper<Map<String, Object>>() {
			@Override
			public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
				// TODO Auto-generated method stub

				Map result = new HashMap();

				int count = rs.getMetaData().getColumnCount();

				for (int i = 1; i <= count; i++) {
					result.put(rs.getMetaData().getColumnLabel(i), rs.getObject(i));
				}
				return result;
			}
		});

	}

	/**
	 * @Title: runTest
	 * @Description: TODO(描述)
	 * @return
	 * @throws SQLException
	 * @see com.fsxgt.datagrid.ds.handler.DbHandler#runTest()
	 * @author liuyi
	 * @throws DsException
	 * @date 2023-12-12 16:06:15
	 */
	@Override
	public boolean runTest() throws DsException {
		// TODO Auto-generated method stub


		

		return true;
	}

	/**  
	 * @Title: close
	 * @Description: TODO(描述) 
	 * @see com.fsxgt.datagrid.ds.handler.DbHandler#close() 
	 * @author liuyi
	 * @date 2024-01-16 08:11:17 
	 */ 
	@Override
	public void close() {
		// TODO Auto-generated method stub
		if(datasource instanceof DruidDataSource) {
			((DruidDataSource)datasource).close();
		}
	}

}
